package cn.zjgsu.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.zjgsu.domain.TbUser;
import cn.zjgsu.utils.SHA1;
import cn.zjgsu.domain.TbAdmin;
import cn.zjgsu.domain.TbCourseFile;
import cn.zjgsu.domain.TbStudent;
import cn.zjgsu.domain.TbTeacher;
import cn.zjgsu.mapping.TbAdminMapper;
import cn.zjgsu.mapping.TbCourseFileMapper;
import cn.zjgsu.mapping.TbStudentMapper;
import cn.zjgsu.mapping.TbTeacherMapper;
import cn.zjgsu.mapping.TbUserMapper;
import cn.zjgsu.service.IAdminService;

@Service
public class AdminServiceImpl implements IAdminService{

	@Autowired
	private TbStudentMapper studentMapper;
	
	@Autowired
	private TbTeacherMapper teacherMapper;
	@Autowired
	private TbCourseFileMapper courseFileMapper;
	@Autowired
	private TbUserMapper userMapper;
	@Autowired
	private TbAdminMapper adminMapper;
	
	@Override
	public int getTeacherCount() {
		
		int count=(int)teacherMapper.getCount();
		return count;
	}
	@Override
	public List<TbTeacher> selectByPage(int startPos, int pageSize) {
		
		List<TbTeacher> list=teacherMapper.selectByPage(startPos, pageSize);
		return list;
	}
	
	
	
	/**
	 * @author yan
	 *教师单个注册
	 */
	@Override
	public void addSingleTeacher(TbTeacher teacher) {
		
		if(teacher!=null){
			TbUser user=teacher.getUser();
			System.out.print(user.getUsername());
		   /* UsersExample usersExample=new UsersExample();
			UsersExample.Criteria criteria=usersExample.createCriteria();
			criteria.andUsernameEqualTo(users.getUsername());
			criteria.andPasswordEqualTo(users.getPassword());*/
			List<TbUser> listUsers = userMapper.selectByNumber(user.getUsernumber());
			if(listUsers.size()==0){
				userMapper.insertSelective(user);	
				teacher.setFkUser(user.getId());
				teacherMapper.insertSelective(teacher);
				}
			}
		
	}
	/**
	 * @author yan
	 *数据库里链表操作删除两个表中的teacher记录
	 */
	@Override
	public void deleteTeacher(int pkTeacher) {
		teacherMapper.deleteByPrimaryKey((long)pkTeacher);
		
	}
	
	/**
	 * @author yan
	 *教师重置密码
	 */
	@Override
	public void updateTeacherPwd(int pkTeacher) {
		
		TbTeacher teacher =teacherMapper.selectByPrimaryKey((long)pkTeacher);
		int pkUser=teacher.getFkUser();
		TbUser user= userMapper.selectByPrimaryKey((long)pkUser);
		
		if(teacher.getFkUser()!=null)
		{
			teacher.setTeacherPwd(SHA1.encrypt("123456"));
			user.setPassword(SHA1.encrypt("123456"));
		  /*teacherMapper.updateTeacherPwd((long)pkTeacher);*/
		 teacherMapper.updateByPrimaryKeySelective(teacher);
		 userMapper.updateByPrimaryKeySelective(user);
		}
		
		/**
		 * @author yan
		 *获得学生用户总数
		 */	
	}
	@Override
	public int getStudentCount() {
		int count=(int)studentMapper.getCount();
		return count;
	}
	
	/**
	 * @author yan
	 *学生用户分页
	 */	
	@Override
	public List<TbStudent> selectByPage2(int startPos, int pageSize) {
		
		List<TbStudent> list=studentMapper.selectByPage(startPos, pageSize);
		return list;
	}
	
	/**
	 * @author yan
	 *单个学生注册
	 */	
	@Override
	public void addSingleStudent(TbStudent student) {
		if(student!=null){
			TbUser user=student.getUser();
  
			List<TbUser> listUsers = userMapper.selectByNumber(user.getUsernumber());
			if(listUsers.size()==0){
				userMapper.insertSelective(user);	
				student.setFkUser(user.getId());
				studentMapper.insertSelective(student);
				}
			}
		
	}
	/**
	 * @author yan
	 *数据库里链表操作删除两个表中的student记录
	 */
	@Override
	public void deleteStudent(int pkStudent) {
		
		studentMapper.deleteByPrimaryKey((long) pkStudent);
		
	}
	@Override
	public void updateStudentPwd(int pkStudent) {
		TbStudent student =studentMapper.selectByPrimaryKey((long)pkStudent);
		int pkUser=student.getFkUser();
		TbUser user= userMapper.selectByPrimaryKey((long)pkUser);
	
		if(student.getFkUser()!=null)
		{
			student.setStudentPwd(SHA1.encrypt("123456"));
			user.setPassword(SHA1.encrypt("123456"));
		 userMapper.updateByPrimaryKeySelective(user);
		 studentMapper.updateByPrimaryKeySelective(student);
		}
		
	}
	
	
	/**
	 * @author yan
	 *通过Excel注册
	 */
	@Override
	public void impotTeacherExcel(InputStream in,String fileName) throws Exception {
		List<TbTeacher> list;
		if(fileName.contains("xlsx")){
			list=imp_07(in);
		}else{
			list=imp(in);
		}
		 TbUser user;
		for(TbTeacher teacher:list){
			user=teacher.getUser();
			/*UsersExample usersExample=new UsersExample();
			UsersExample.Criteria criteria=usersExample.createCriteria();
			criteria.andUsernameEqualTo(users.getUsername());
			criteria.andPasswordEqualTo(users.getPassword());*/
		/*	List<Users> listUsers = usersMapper.selectByExample(usersExample);*/
			//看文件中的用户是否已经备注册过了
			List<TbUser> listUsers = userMapper.selectByNumber(user.getUsernumber());
		
			if(listUsers.size()==0){
				
				userMapper.insertSelective(user);
				teacher.setFkUser(user.getId());
				teacherMapper.insertSelective(teacher);
			}
		}
	}

	
	
	
	
	/**
	 * @author yan
	 *Excel文件读取xsl
	 */
	
	private List<TbTeacher> imp_07(InputStream in) throws Exception{
		List<TbTeacher> list;
		try {
			list=new ArrayList<TbTeacher>();
			XSSFWorkbook hwb = new XSSFWorkbook(in);
			
			
			// 循环工作表sheet
			for (int num = 0; num < hwb.getNumberOfSheets(); num++) {
				
				XSSFSheet hssfSheet = hwb.getSheetAt(num);
				if (hssfSheet == null) {
					continue;
				}
				// 循环行Row,在excel表中直接就是第二行就上工号名字了
				for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
					TbTeacher teacher = new TbTeacher();
					TbUser users=new TbUser();
					XSSFRow hssfRow = hssfSheet.getRow(row);
					if (hssfRow == null) {
						continue;
					}
					// 循环列Cell
					// for (int cell = 0; cell <= 4; cell++) {
					XSSFCell number = hssfRow.getCell(0);
					if (number == null) {
						continue;
					}
					teacher.setTeacherNum(sub(number.getRawValue()));
					
					XSSFCell name = hssfRow.getCell(1);
					if (number == null) {
						continue;
					}
					String name1=name+"";
					System.out.print(sub(name1));
					teacher.setTeacherName(sub(name1));
					
					XSSFCell password = hssfRow.getCell(2);
					if (number == null) {
						continue;
					}
					String password1=password+"";
					new SHA1();
					password1=SHA1.encrypt(password1);
					teacher.setTeacherPwd((sub(password1)));
					users.setUsernumber(sub(number.getRawValue()));
					users.setPassword((sub(password1)));
					users.setUsername(name1);
					users.setRole("2");
					teacher.setUser(users);
					list.add(teacher);
				}
			}
		} catch (IOException e) {
			throw new Exception("读取excel有误！");
		}
		
		return list;
	}
	
	
	
	/**
	 * @author yan
	 *Excel读取xxsl
	 *
	 */

	public List<TbTeacher> imp(InputStream in) throws Exception {
		List<TbTeacher> list;
		try {
			list=new ArrayList<TbTeacher>();
			HSSFWorkbook hwb = new HSSFWorkbook(in);
			
			
			// 循环工作表sheet
			for (int num = 0; num < hwb.getNumberOfSheets(); num++) {
				
				HSSFSheet hssfSheet = hwb.getSheetAt(num);
				if (hssfSheet == null) {
					continue;
				}
				// 循环行Row
				for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
					TbTeacher teacher = new TbTeacher();
					TbUser user=new TbUser();
					HSSFRow hssfRow = hssfSheet.getRow(row);
					if (hssfRow == null) {
						continue;
					}
					// 循环列Cell
					// for (int cell = 0; cell <= 4; cell++) {
					HSSFCell number = hssfRow.getCell(0);
					if (number == null) {
						continue;
					}
					teacher.setTeacherNum(sub(getValue(number)));
					
					HSSFCell name = hssfRow.getCell(1);
					if (number == null) {
						continue;
					}
					String name1=name+"";
					teacher.setTeacherName(sub(name1));
					
					HSSFCell password = hssfRow.getCell(2);
					if (number == null) {
						continue;
					}
					String password1=password+"";
					new SHA1();
					password1=SHA1.encrypt(password1);
					teacher.setTeacherPwd((sub(password1)));
					user.setUsernumber(sub(getValue(number)));
					user.setPassword((sub(password1)));
					user.setUsername(name1);
					user.setRole("2");		
					teacher.setUser(user);
					list.add(teacher);
				}
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			throw new Exception("读取excel有误！");
		}
		
		return list;
	}
			
			public String sub(Object o) {
				String s = o + "";
				if (s.contains(".")) {
					s = s.substring(0, s.indexOf("."));
				}
				return s;
			}

			/*
			 * 得到Excel表中的值
			 * 
			 * @param hssfCell Excel中的每一个格子
			 * 
			 * @return Excel中每一个格子中的值
			 */
		
			private String getValue(HSSFCell hssfCell) {
				if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
					// 返回布尔类型的值
					return String.valueOf(hssfCell.getBooleanCellValue());
				} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
					// 返回数值类型的值
					return String.valueOf(hssfCell.getNumericCellValue());
				} else {
					// 返回字符串类型的值
					return String.valueOf(hssfCell.getStringCellValue());
				}
			}
			
			
			
		/**
		* @author yan
		*批量导入学生
		*
		*/	
	@Override
   public void impotStudentExcel(InputStream in, String fileName) throws Exception {
			List<TbStudent> list;
			if(fileName.contains("xlsx")){
				list=impStudent_07(in);
			}else{
				list=impStudent(in);
			    }
				 TbUser user;
				for(TbStudent student:list){
					user=student.getUser();
					
					/*UsersExample usersExample=new UsersExample();
					UsersExample.Criteria criteria=usersExample.createCriteria();
					criteria.andUsernameEqualTo(users.getUsername());
					criteria.andPasswordEqualTo(users.getPassword());*/
				/*	List<Users> listUsers = usersMapper.selectByExample(usersExample);*/
					//看文件中的用户是否已经备注册过了
					List<TbUser> listUsers = userMapper.selectByNumber(user.getUsernumber());
				
					if(listUsers.size()==0){
						System.out.print(user.getRole());
						System.out.print(user.getUsernumber());
						System.out.print(user.getUsername());
						System.out.print(user.getPassword());
						userMapper.insertSelective(user);
						student.setFkUser(user.getId());
						studentMapper.insertSelective(student);
				}
			}
		}
		private List<TbStudent> impStudent(InputStream in) throws Exception {
			List<TbStudent> list;
			try {
				list=new ArrayList<TbStudent>();
				HSSFWorkbook hwb = new HSSFWorkbook(in);
				
				
				// 循环工作表sheet
				for (int num = 0; num < hwb.getNumberOfSheets(); num++) {
					
					HSSFSheet hssfSheet = hwb.getSheetAt(num);
					if (hssfSheet == null) {
						continue;
					}
					// 循环行Row
					for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
						TbStudent student = new TbStudent();
						TbUser user=new TbUser();
						HSSFRow hssfRow = hssfSheet.getRow(row);
						if (hssfRow == null) {
							continue;
						}
						// 循环列Cell
						// for (int cell = 0; cell <= 4; cell++) {
						HSSFCell number = hssfRow.getCell(0);
						if (number == null) {
							continue;
						}
						student.setStudentNum(sub(getValue(number)));
						
						HSSFCell name = hssfRow.getCell(1);
						if (number == null) {
							continue;
						}
						String name1=name+"";
						student.setStudentName(sub(name1));
						
						HSSFCell password = hssfRow.getCell(2);
						if (number == null) {
							continue;
						}
						String password1=password+"";
						new SHA1();
						password1=SHA1.encrypt(password1);
						student.setStudentPwd((sub(password1)));
						user.setUsernumber(sub(getValue(number)));
						user.setPassword((sub(password1)));
						user.setUsername(name1);
						user.setRole("3");		
						student.setUser(user);
						list.add(student);
					}
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				throw new Exception("读取excel有误！");
			}
			
			return list;
		}
		private List<TbStudent> impStudent_07(InputStream in) throws Exception {
			List<TbStudent> list;
			try {
				list=new ArrayList<TbStudent>();
				XSSFWorkbook hwb = new XSSFWorkbook(in);
				
				
				// 循环工作表sheet
				for (int num = 0; num < hwb.getNumberOfSheets(); num++) {
					
					XSSFSheet hssfSheet = hwb.getSheetAt(num);
					if (hssfSheet == null) {
						continue;
					}
					// 循环行Row,在excel表中直接就是第二行就上工号名字了
					for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
						TbStudent student = new TbStudent();
						TbUser users=new TbUser();
						XSSFRow hssfRow = hssfSheet.getRow(row);
						if (hssfRow == null) {
							continue;
						}
						// 循环列Cell
						// for (int cell = 0; cell <= 4; cell++) {
						XSSFCell number = hssfRow.getCell(0);
						if (number == null) {
							continue;
						}
						System.out.print(sub(number.getRawValue()));
						student.setStudentNum(sub(number.getRawValue()));
						
						XSSFCell name = hssfRow.getCell(1);
						if (number == null) {
							continue;
						}
						String name1=name+"";
						System.out.print(sub(name1));
						student.setStudentName(sub(name1));
						
						XSSFCell password = hssfRow.getCell(2);
						if (number == null) {
							continue;
						}
						String password1=password+"";
						new SHA1();
						password1=SHA1.encrypt(password1);
						System.out.print((sub(password1)));
						users.setUsernumber(sub(number.getRawValue()));
						student.setStudentPwd((sub(password1)));
						users.setPassword((sub(password1)));
						users.setUsername(name1);
						users.setRole("3");
						student.setUser(users);
						list.add(student);
					}
				}
			} catch (IOException e) {
				throw new Exception("读取excel有误！");
			}
			
			return list;
		}
		
		
		
		
		/**
		 * 按照工号查询教师
		 * 
		 *
		 */
		@Override
		public List<TbTeacher> searchByTeacherNum(String teacherNum) {
			
			List<TbTeacher> list=teacherMapper.searchByTeacherNum(teacherNum);
			return list;
		}
		@Override
		public List<TbTeacher> searchByTeacherName(String teacherName) {
			
			List<TbTeacher> list=teacherMapper.searchByTeacherName(teacherName);
			return list;
		}
		
		
		
		
		
		/**
		 * 按照工号查询学生
		 * 
		 *
		 */
		@Override
		public List<TbStudent> searchByStudentNum(String studentNum) {
			List<TbStudent> list=studentMapper.searchByStudentNum(studentNum);
			return list;
		}
		@Override
		public List<TbStudent> searchByStudentName(String studentName) {
			List<TbStudent> list=studentMapper.searchByStudentName(studentName);
			return list;
		}
		@Override
		public void addAdmin(TbAdmin admin) {
			if(admin!=null){
				TbUser user=admin.getUser();
	  
				List<TbUser> listUsers = userMapper.selectByNumber(user.getUsernumber());
				if(listUsers.size()==0){
					userMapper.insertSelective(user);	
					admin.setFkUser(user.getId());
					adminMapper.insertSelective(admin);
					}
				}
			
		}
		@Override
		public void importCourseFile(TbCourseFile courseFile) {
	
			courseFileMapper.insertSelective(courseFile);
			
		}
		
		@Override
		public int getCourseFileCount() {
			
			int count=(int)courseFileMapper.getCount();
			return count;
		}
		
		/**
		 * 用于分页获取老师的列表
		 *
		 */
		@Override
		public List<TbCourseFile> selectByPage3(int startPos, int pageSize) {
			
			List<TbCourseFile> list=courseFileMapper.selectByPage(startPos, pageSize);
			return list;
		}
		
		
		
		/**
		 * 根据课表id删除学生记录
		 *
		 */

		@Override
		public void deleteCourseFile(int pkCourseFile) {
			
			courseFileMapper.deleteByPrimaryKey((long) pkCourseFile);
			
		}
		
		
		
		/**
		 * 根据课表id查询课表单条记录
		 *
		 */

		@Override
		public TbCourseFile selectCourseFileByPrimaryKey(long pkCourseFile) {
			
			TbCourseFile courseFile =courseFileMapper.selectByPrimaryKey((long)pkCourseFile);
			return courseFile;
		}
	

}
